package com.duleme.dao;

import com.duleme.common.DBUtils;
import com.duleme.common.JsonUtils;
import com.duleme.entity.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//import java.util.Date;

public class BookDao {


    //根据店铺名称返回店铺下所有书籍
    public List<Book> findAllBooksInShop(String shopName) {
        String sql = "SELECT * FROM book WHERE shopName=?";
        try {
            List<Book> bookList = new ArrayList<Book>();
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, shopName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setCover(rs.getString("cover"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setCategoryId(rs.getInt("categoryId"));
                book.setCategory(rs.getString("category"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                bookList.add(book);
            }
            DBUtils.close(connection, ps, rs);
            return bookList;

        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }
    }

    //插入一本书
    public boolean insertBook(Book book) {
        String sql = "INSERT INTO book (synopsis,bookName,author,press,category,publishDate,categoryId,shopId,shopName,sell,remain,price,cover" +
                ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, book.getSynopsis());
            ps.setString(2, book.getBookName());
            ps.setString(3, book.getAuthor());
            ps.setString(4, book.getPress());
            ps.setString(5, book.getCategory());
            ps.setDate(6, new Date(book.getPublishDate().getTime()));
            ps.setInt(7, book.getCategoryId());
            ps.setInt(8, book.getShopId());
            ps.setString(9, book.getShopName());
            ps.setInt(10, book.getSell());
            ps.setInt(11, book.getRemain());
            ps.setDouble(12, book.getPrice());
            ps.setString(13, book.getCover());
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.getStackTrace();
            return false;
        }

    }

    //根据店铺id返回店铺类所有书籍列表
    public List<Book> findBooksByShopId(int shopId) {
        List<Book> list = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE shopId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, shopId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setCover(rs.getString("cover"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setCategoryId(rs.getInt("categoryId"));
                book.setCategory(rs.getString("category"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                list.add(book);
            }
            System.out.println(JsonUtils.toJson(list));
            DBUtils.close(connection, ps, rs);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    //删除一本书根据id
    public boolean deleteBookById(int bookId, int shopId) throws SQLException {
        String sql = "DELETE FROM book WHERE bookId=?";
        String sql2 = "UPDATE shop SET count=count-1 WHERE shopId=?";
        Connection connection = DBUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        try {
            connection.setAutoCommit(false);
            ps.setInt(1, bookId);
            ps2.setInt(1, shopId);
            ps.executeUpdate();
            ps2.executeUpdate();
            connection.commit();
            return true;
        } catch (Exception e) {
            e.getStackTrace();
            connection.rollback();
            return false;
        } finally {
            if (connection != null) connection.close();
            if (ps !=null)ps.close();
            if (ps2!=null)ps2.close();
        }
    }

    //根据bookId查找书
    public Book findBookByBookId(int bookId) {
        String sql = "SELECT * FROM book WHERE bookId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, bookId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setCover(rs.getString("cover"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setCategory(rs.getString("category"));
                book.setCategoryId(rs.getInt("categoryId"));
                DBUtils.close(connection, ps, rs);
                return book;
            }
            DBUtils.close(connection, ps, rs);
            return null;
        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }
    }

    //根据书名查询书
    public List<Book> findBookByName(String name) {
        List<Book> bookList = new ArrayList<Book>();
        String sql = "SELECT * FROM book WHERE LOCATE(?,bookName)>0";
        System.out.println(name);
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setCover(rs.getString("cover"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setCategory(rs.getString("category"));
                book.setCategoryId(rs.getInt("categoryId"));
                bookList.add(book);
            }
            DBUtils.close(connection, ps, rs);
            return bookList;
        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }

    }

    public boolean updateBook(Book book) {
        String sql = "UPDATE book SET bookName=?,synopsis=?,author=?,press=?,publishDate=?,cover=?,remain=?,price=? where bookId=?";

        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps;
            ps = connection.prepareStatement(sql);
            ps.setString(1, book.getBookName());
            ps.setString(2, book.getSynopsis());
            ps.setString(3, book.getAuthor());
            ps.setString(4, book.getPress());
            ps.setDate(5, new Date(book.getPublishDate().getTime()));
            ps.setString(6, book.getCover());
            ps.setInt(7, book.getRemain());
            ps.setDouble(8, book.getPrice());
            ps.setInt(9, book.getBookId());
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.getStackTrace();
            return false;
        }
    }

    //更新价格，销量，剩余数量(值为-1时保持原值）
    public boolean updateBookRemain(int bookId, double price, int remain) {
        String sql = "UPDATE book SET price=?,remain=? WHERE bookId=?";

        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setDouble(1, price);
            ps.setInt(2, remain);
            ps.setInt(3, bookId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Book> findBooksByCategory(String category) {
        String sql="SELECT * FROM book WHERE category=?";
        try {
            List<Book> list=new ArrayList<>();
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, category);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setCover(rs.getString("cover"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setCategory(rs.getString("category"));
                book.setCategoryId(rs.getInt("categoryId"));
                list.add(book);
            }
            DBUtils.close(connection, ps, rs);
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }

    public int findBookIdByBookNameAndShopName(String bookName, String shopName) {
        String sql = "SELECT bookId FROM book WHERE bookName=? and shopName=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, bookName);
            ps.setString(2, shopName);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int id = Integer.parseInt(rs.getString("bookId"));
                DBUtils.close(connection, ps, rs);
                return id;
            }
            DBUtils.close(connection, ps, rs);
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    //批量删除多个书籍
    public boolean deleteBooksById(String[] id) {
        String sql = "DELETE FROM book WHERE bookId=?";
        try {
            Connection connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                ps.setInt(1, Integer.parseInt(id[i]));
                ps.addBatch();
            }
            int[] result = ps.executeBatch();
            connection.commit();
            for (int r : result) {
                if (r == 0) {
                    DBUtils.close(connection, ps, null);
                    return false;
                }
            }
            DBUtils.close(connection, ps, null);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    //后台管理修改书籍信息
    public boolean updateBookInfo(Book book) {
        String sql = "UPDATE book SET bookName=?,synopsis=?,author=?,publishDate=?,cover=?,remain=?,price=?,category=?,categoryId=?,press=? WHERE bookId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, book.getBookName());
            ps.setString(2, book.getSynopsis());
            ps.setString(3, book.getAuthor());
            ps.setDate(4, new Date(book.getPublishDate().getTime()));
            ps.setString(5, book.getCover());
            ps.setInt(6, book.getRemain());
            ps.setDouble(7, book.getPrice());
            ps.setString(8, book.getCategory());
            ps.setInt(9, book.getCategoryId());
            ps.setString(10, book.getPress());
            ps.setInt(11, book.getBookId());
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }

    //返回库中的所有书
    public List<Book> findAllBooks(){
        List<Book> bookList = new ArrayList<Book>();
        String sql = "SELECT * FROM book ";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setCover(rs.getString("cover"));
                book.setPrice(rs.getDouble("price"));
                book.setRemain(rs.getInt("remain"));
                book.setSell(rs.getInt("sell"));
                book.setShopName(rs.getString("shopName"));
                book.setShopId(rs.getInt("shopId"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setSynopsis(rs.getString("synopsis"));
                book.setPublishDate(rs.getDate("publishDate"));
                book.setCategory(rs.getString("category"));
                book.setCategoryId(rs.getInt("categoryId"));
                bookList.add(book);
            }
            DBUtils.close(connection, ps, rs);
            return bookList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void main(String[] args) {
//      BookDao dao=new BookDao();
//      List<BookCategory> list=dao.findAllChild("科技");
//      if(list==null)System.out.println("未找到");
//      else{
//          for (BookCategory bc:list){
//              System.out.println(bc.getCategory());
//          }
//      }
//      String sql="select * from bookcategory where category=?";
//      try{
//          Connection connection=DBUtils.getConnection();
//          PreparedStatement ps=connection.prepareStatement(sql);
//          ps.setString(1,"科技");
//          ResultSet rs=ps.executeQuery();
//          if(rs.next()){
//              System.out.println(rs.getInt("categoryId"));
//
//          }
//      }catch (Exception e){
//          e.getStackTrace();
//      }
//        BookDao dao=new BookDao();
//        List<BookCategory> c=dao.findAllChild("科技");
//        for(BookCategory cate:c){
//            System.out.println(cate.getCategory());
//        }
////
//        BookDao dao = new BookDao();
//        System.out.println(dao.updateBookRemain(135, 13, 0));
//        book.setBookName("c++从入门到放弃");
//        book.setAuthor("lym");
//        book.setCategory("计算机");
//        book.setPress("燕山大学出版社");
//        book.setCategoryId(5);
//        book.setSynopsis("3天入门，7天放弃");
//        book.setCover("/a");
//        //book.setPrice(23.2);
//        book.setRemain(10);
//        book.setSell(30);
//        book.setPublishDate(new java.util.Date());
//        book.setShopId(4);
//        book.setShopName("409");

//        book.setPublishDate(new java.util.Date());
//        BookDao dao = new BookDao();
//        boolean a = dao.insertBook(book);
//        System.out.println(a);
//        Goods goods=new Goods();
//        goods.setGoodsName("409upup");
//        goods.setSell(12);
//        goods.setRemain(26);
//        goods.setShopId(3);
//        goods.setGoodsImage("");
//        goods.setPrice(666.66);
//        goods.setBookName("coding大法好啊");
//        goods.setBookId(2);
//        BookDao dao=new BookDao();
//        System.out.println(dao.insertGoods(goods));
        BookDao dao = new BookDao();
//        System.out.println(dao.findAllBooks().size());
        Book book=new Book();
        book.setPublishDate(new java.util.Date());
        System.out.println(JsonUtils.toJson(book));
        //List<Book> list=dao.findBooksByShopId(1);
//        if (list != null) {
//            BookJson bookJson = new BookJson();
//            bookJson.setData(list);
//            bookJson.setCount(list.size());
//            bookJson.setCode(0);
//            System.out.println(JsonUtils.toJson(bookJson));
//        }
    }
}
